package com.telecom.cms.service;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.HashMap;

import com.telecom.cms.dao.CimPageAtomDAO;
import com.telecom.cms.dao.CimPageDAO;
import com.telecom.cms.dao.SysGetIdDAO;
import com.telecom.cms.dao.SysLocLatnDAO;
import com.telecom.cms.dao.Dao;
import com.telecom.cms.dao.DaoImpStd;
import com.telecom.cms.dto.CimPageAtom;
import com.telecom.cms.dto.CimPage;

import com.telecom.cms.dto.SEG_BIZ_ATOM_DEFDTO;
import com.telecom.cms.dao.SEG_BIZ_ATOM_DEFDAO;
import com.telecom.cms.util.Constant;
import com.telecom.cms.util.CurrentTime;

public class CimPageAtomMgr {
	public void save(CimPageAtom campadv) {
		(new CimPageAtomDAO()).insert(campadv);
	}

	public void delete(CimPageAtom campadv) {
		(new CimPageAtomDAO()).delete(campadv);
	}
	
	public void refresh() {
		(new CimPageAtomDAO()).findAllForce();
	}
	
	public void deletebypageid(String pageid){
		(new CimPageAtomDAO()).deletebypageid(pageid);
	}

	public List findall(String pageId) {
		List result = (new CimPageAtomDAO()).findAll(pageId);
		return result;
	}

	public String getinfo(String pageId,String custid,String clustertype,String latnid) {
		String latncd=(new SysLocLatnDAO()).findById("latn_id", latnid).getLatn_Cd();
		String clutype="cust_id";
		if(clustertype.equals("1"))
			clutype="prd_inst_id";
		
		String billingcycleid=CurrentTime.getAccuPreMonthAll();
		
		int iflag = 0;
		List pageatomlist = (new CimPageAtomDAO()).findAll(pageId);
		CimPage cimpage = (new CimPageDAO()).findById(pageId);
		StringBuffer result = new StringBuffer();
		StringBuffer sql = new StringBuffer();
		StringBuffer tabsql = new StringBuffer();

		CimPageAtom inst = null;
		SEG_BIZ_ATOM_DEFDTO dto = null;
		Dao dao = new DaoImpStd();
		SEG_BIZ_ATOM_DEFDAO atomdao = new SEG_BIZ_ATOM_DEFDAO();
		String db = (new Constant()).getPara("DATABASE");
		int hasvalueflag=0;

		HashMap seglist=new HashMap();//user hashmap to reduce the times to get BIZ_ATOM
		HashMap tablist=new HashMap();
		HashMap srctablist=new HashMap();
		
		//result.append("<tr>");
		
		for (iflag = 0; iflag < pageatomlist.size(); iflag++) {
			inst = (CimPageAtom) pageatomlist.get(iflag);
			dto = (SEG_BIZ_ATOM_DEFDTO) atomdao.findById(inst.getBizAtomId());
			
			//System.out.println("the key is :"+inst.getBizAtomId());
			if(seglist.get(""+dto.getBIZ_ATOM_ID())==null)
			{
				 seglist.put(""+dto.getBIZ_ATOM_ID(), dto);
			}
			if (tablist.get(""+dto.getBAS_TABLE_NAME().trim())==null)
			{
				tablist.put(""+dto.getBAS_TABLE_NAME().trim(), dto);
			}
			
			//else
				//System.out.println(""+tablist.get(""+dto.getBAS_TABLE_NAME().trim()));
		}
		ArrayList [] titlelist=new  ArrayList[tablist.size()];
		ArrayList [] resultlist=new  ArrayList[tablist.size()];
		
		for(int i=0;i<tablist.size();i++)
		{
			titlelist[i]=new ArrayList();
			resultlist[i]=new ArrayList();
		}
		
 		//System.out.println(""+tablist.size());
		Iterator iterator = seglist.keySet().iterator();
		Iterator iterator2 = tablist.keySet().iterator();
		ResultSet rs2;
		int listflag=-1;
		while(iterator2.hasNext()) {
			srctablist=new HashMap();
			sql = new StringBuffer();
			tabsql = new StringBuffer();
			sql.append("SELECT DISTINCT ");
			listflag++;
			dto = (SEG_BIZ_ATOM_DEFDTO) (tablist.get(iterator2.next()));
			String bas_table=dto.getBAS_TABLE_NAME().trim();
			//System.out.println(bas_table);
			iterator = seglist.keySet().iterator();
		while(iterator.hasNext()) {
	    	//System.out.println(seglist.get(iterator.next()));
	    	dto = (SEG_BIZ_ATOM_DEFDTO) (seglist.get(iterator.next()));
	    	//System.out.println(dto.getBAS_TABLE_NAME().trim());
	    	if(!dto.getBAS_TABLE_NAME().trim().equals(bas_table))
	    		continue;
			/*if (cimpage.getShowType().equals("h"))
				result.append("<th>" + dto.getBIZ_ATOM_NAME() + "</th>");*/
	    	
	    	titlelist[listflag].add(dto.getBIZ_ATOM_NAME());
	    	//System.out.println(dto.getBIZ_ATOM_FLAG());
			if(dto.getBIZ_ATOM_FLAG().trim().equals("money"))
			   sql.append(dto.getBAS_TABLE_NAME()+"."+dto.getBAS_TABLE_COL_NAME()+"/100");
			else if(dto.getBIZ_ATOM_FLAG().trim().equals("time"))
			   sql.append(dto.getBAS_TABLE_NAME()+"."+dto.getBAS_TABLE_COL_NAME()+"/60");
			else if(dto.getSRC_TABLE_COL_NAME()==null||dto.getSRC_TABLE_COL_NAME().trim().equals(""))
			   sql.append(dto.getBAS_TABLE_NAME()+"."+dto.getBAS_TABLE_COL_NAME());
			else
			{
			   sql.append(dto.getCDE_SRC_TABLE_NAME().trim()+"."+dto.getSRC_TABLE_COL_NAME().trim());
			   if (srctablist.get(""+dto.getCDE_SRC_TABLE_NAME().trim())==null)
				{
					srctablist.put(""+dto.getCDE_SRC_TABLE_NAME().trim(), dto);
				
			       tabsql.append(" left join "+dto.getCDE_SRC_TABLE_NAME().trim()+" on "
					     +dto.getBAS_TABLE_NAME().trim()+"."+dto.getBAS_TABLE_COL_NAME().trim()+"="
					     +dto.getCDE_SRC_TABLE_NAME().trim()+"."+dto.getBAS_TABLE_COL_NAME().trim()+" ");
			       
				   if(dto.getCDE_SRC_TABLE_NAME().trim().toUpperCase().indexOf("PRD_INST_PFL")>-1)
				    	tabsql.append(" and cast("+dto.getCDE_SRC_TABLE_NAME().trim()+".std_prd_inst_stat_cd as char(2)) not in ('11') and "
				    			+dto.getCDE_SRC_TABLE_NAME().trim()+".sys_id=1101 ");
				}
			}
			
			sql.append(",");
			
		}
		
		   
		
			if(cimpage.getShowType().equals("x"))
			{
				titlelist[listflag].add("周期");
				sql.append(""+bas_table+".billing_cycle_id,");
			}

			sql.append("0 from "+bas_table);
			sql.append(" "+tabsql+" where ");
		    sql.append(bas_table+"."+clutype+"='"+custid+"' ");
		    
		    /*if(cimpage.getShowType().equals("x"))
				sql.append(" and "+bas_table+".billing_cycle_id="+billingcycleid);*/

			if(bas_table.toUpperCase().indexOf((new Constant()).getPara("BAS_PRD_INST_FILTER"))>-1)
				sql.append(" and "+bas_table+"."+(new Constant()).getPara("PRD_INST_FILTER"));
			
			if(bas_table.toUpperCase().indexOf((new Constant()).getPara("BILLING_TAB_FILTER1"))>-1
					||bas_table.toUpperCase().indexOf((new Constant()).getPara("BILLING_TAB_FILTER2"))>-1
					||bas_table.toUpperCase().indexOf((new Constant()).getPara("BILLING_TAB_FILTER3"))>-1)
			{
				if(cimpage.getShowType().equals("v")|| (cimpage.getShowType().equals("t")))
					sql.append(" and "+bas_table+".billing_cycle_id='"+billingcycleid+"'");
					//sql.append(" QUALIFY ROW_NUMBER() over (PARTITION BY "+bas_table+"."+clutype+" ORDER BY "+bas_table+".billing_cycle_id DESC) = 1");
			    else
			      sql.append(" ORDER BY "+bas_table+".billing_cycle_id DESC ");
			}
			
			/*if(tablist.size()==0)
			{
				tablist.add(dto);
				//System.out.println( dto.getBAS_TABLE_NAME());
			}
			else
			{
				int i=-1;
				for (i=0;i<tablist.size();i++)
					if(((SEG_BIZ_ATOM_DEFDTO)tablist.get(i)).getBAS_TABLE_NAME().trim().equals(dto.getBAS_TABLE_NAME().trim()))
						break;
				if (i==tablist.size())
				{
					//System.out.println( dto.getBAS_TABLE_NAME());
					tablist.add(dto);
				}
			}
			
			if (iflag < pageatomlist.size() - 1)
				sql.append(",");*/
		

		
		try {
 			
			rs2 = dao.select((new Constant()).changeLatnId(latncd, sql.toString()));
			ResultSetMetaData meta;
			meta=rs2.getMetaData();
			int cc =meta.getColumnCount();
			while (rs2.next()) {
				for(int i=0;i<cc-1;i++)
					resultlist[listflag].add(rs2.getString(i+1)==null?"":rs2.getString(i+1));
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			result.append((new Constant()).getPara("NO_DATA"));
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		}
		
		
		//begin write data
		System.out.println(cimpage.getShowType());
		if(cimpage.getShowType().equals("v")){
	    int vflag=0;
	    result.append("<tr>");
		for(int i=0;i<titlelist.length;i++)
		{
			for(int j=0;j<titlelist[i].size();j++)
			{
				result.append("<td><img src='images/li2.gif' />"+
						titlelist[i].get(j).toString()
						+":");
				if(titlelist[i].get(j).toString().toUpperCase().indexOf("客户编号")>-1)
					   result.append("<a href='custview.do?method=list&custid="+resultlist[i].get(j)+"'>"
							+ resultlist[i].get(j) + "</a></td>");
				else
				      result.append((resultlist[i].get(j)==null?"":resultlist[i].get(j).toString())+"</td>");
				vflag=(vflag+1)%2;
				if(0==vflag) result.append("</tr></tr>");
			}
		}
		if(1==vflag)
			result.append("<td></td>");
		result.append("</tr>");
		}
		else if(cimpage.getShowType().equals("h")||cimpage.getShowType().equals("t")){
			
			result.append("<tr>");
			for(int i=0;i<titlelist.length;i++)
			{
				for(int j=0;j<titlelist[i].size();j++)
				{
					result.append(" <th>"+
							titlelist[i].get(j).toString()
							+"</th>");
				}
			}
			result.append("</tr>");
			//System.out.println(resultlist[0].size());
			int billingcnt=resultlist[0].size()/titlelist[0].size();
			for(int j=0;j<billingcnt;j++)
			{
				result.append("<tr>");
			    for(int i=0;i<titlelist.length;i++)
			    {
			    	for(int k=0;k<titlelist[i].size();k++)
					{
			        	result.append("<td> ");
			        	if(titlelist[i].get(k).toString().toUpperCase().indexOf("接入号码")>-1)
						    result.append("<a href='prdview.do?method=list&nbrtype=accsnbr&nbr="
						    		+resultlist[i].get(k+titlelist[i].size()*j)+"'>" + resultlist[i].get(k+titlelist[i].size()*j) + "</a></td>");
						else if(titlelist[i].get(k).toString().toUpperCase().indexOf("PRD_INST_ID")>-1)
						    result.append("<a href='prdview.do?method=list&nbrtype=prdinstid&nbr="+resultlist[i].get(k+titlelist[i].size()*j)+"'>" + resultlist[i].get(k+titlelist[i].size()*j) + "</a></td>");
						else
			        	result.append((resultlist[i].get(k+titlelist[i].size()*j)==null?"":resultlist[i].get(k+titlelist[i].size()*j).toString())
							+ "</td>");
					}
				}
		    	result.append("</tr>");
			}
			
		}
        else if(cimpage.getShowType().equals("x")){
			
			result.append("<tr>");
			result.append("<th> 周期 </th>");
			result.append("<th> 项目  </th>");
			result.append("<th> 值 </th>");
			result.append("</tr>");
			
			//System.out.println(resultlist[0].size());
			int billingcnt=resultlist[0].size()/titlelist[0].size();
			for(int j=0;j<billingcnt;j++)
			{
				float totalsum=0;
				String mybilling="";
			    for(int i=0;i<titlelist.length;i++)
			    {
			    	for(int k=0;k<titlelist[i].size();k++)
					{
			    		if(titlelist[i].get(k).toString().equals("周期"))continue;
			    		result.append("<tr>");
			    		mybilling=(resultlist[i].get(titlelist[i].size()*(j+1)-1)==null?"":resultlist[i].get(titlelist[i].size()*(j+1)-1).toString());
			    		result.append("<td>"
			    				+ mybilling +"</td>");
			    		result.append("<td>"
							    + (titlelist[i].get(k)==null?"":titlelist[i].get(k).toString())
								+ "</td>");
			        	result.append("<td>"
						    + (resultlist[i].get(k+titlelist[i].size()*j)==null?"":resultlist[i].get(k+titlelist[i].size()*j).toString())
							+ "</td>");
			        	result.append("</tr>");
			        	totalsum+=Float.parseFloat(resultlist[i].get(k+titlelist[i].size()*j)==null?"0.0":resultlist[i].get(k+titlelist[i].size()*j).toString());
			        	}
				}
			    result.append("<tr>");
				result.append("<td><b>"+mybilling+"</b></td>");
				result.append("<td><b>合计</b></td>");
				result.append("<td><b>"+totalsum+"</b></td>");
				result.append("</tr>");
			}
		}
		
		/*if(cimpage.getShowType().equals("x"))
			sql.append(","+dto.getBAS_TABLE_NAME()+".billing_cycle_id ");
		
		sql.append(" from ");
		for(int i=0;i<tablist.size();i++)
    	  {
			
			sql.append( ((SEG_BIZ_ATOM_DEFDTO)tablist.get(i)).getBAS_TABLE_NAME());
    	    if(i!=tablist.size()-1)
    	    	sql.append(",");
    	  }
		sql.append(" "+tabsql+" where ");
		for(int i=0;i<tablist.size();i++)
		{
		    sql.append(((SEG_BIZ_ATOM_DEFDTO)tablist.get(i)).getBAS_TABLE_NAME()+"."+clutype+"='"+custid+"' ");
		    if(i!=tablist.size()-1)
    	    	sql.append(" and ");
		}
		
		if(dto.getBAS_TABLE_NAME().toUpperCase().indexOf((new Constant()).getPara("BAS_PRD_INST_FILTER"))>-1)
			sql.append(" and "+dto.getBAS_TABLE_NAME()+"."+(new Constant()).getPara("PRD_INST_FILTER"));
		
		if(dto.getBAS_TABLE_NAME().toUpperCase().indexOf((new Constant()).getPara("BILLING_TAB_FILTER1"))>-1
				||dto.getBAS_TABLE_NAME().toUpperCase().indexOf((new Constant()).getPara("BILLING_TAB_FILTER2"))>-1)
		{
			if(cimpage.getShowType().equals("v"))
				sql.append(" QUALIFY ROW_NUMBER() over (PARTITION BY "+dto.getBAS_TABLE_NAME()+"."+clutype+" ORDER BY "+dto.getBAS_TABLE_NAME()+".billing_cycle_id DESC) = 1");
			  //sql.append(" QUALIFY ROW_NUMBER() over (PARTITION BY "+clutype+" ORDER BY "+dto.getBAS_TABLE_NAME()+".billing_cycle_id DESC) = 1");
		    else
		      sql.append(" ORDER BY "+dto.getBAS_TABLE_NAME()+".billing_cycle_id DESC ");
		}
		
		if (cimpage.getShowType().equals("v"))
			result.append("<th colspan=2>" + cimpage.getPageName() + "</th>");
		result.append("</tr>");
        
		
		try {
			
			rs2 = dao.select((new Constant()).changeLatnId(latncd, sql.toString()));
			if (cimpage.getShowType().equals("v"))
				result.append("<tr>");
			
			while (rs2.next()) {
				hasvalueflag=1;
				// write info in rows
				if (cimpage.getShowType().equals("h")) {
					result.append("<tr>");
					for (int i = 1; i <= iflag; i++)
					{
						inst = (CimPageAtom) pageatomlist.get(i - 1);
						
						//get biz_atom info from hashmap directly
						if(seglist.get(""+inst.getBizAtomId())!=null)
							dto=(SEG_BIZ_ATOM_DEFDTO)seglist.get(""+inst.getBizAtomId());
						else
						{
						   dto = (SEG_BIZ_ATOM_DEFDTO) atomdao.findById(inst.getBizAtomId());
						   seglist.put(""+inst.getBizAtomId(), dto);
						}
						
						if(dto.getBAS_TABLE_COL_NAME().toUpperCase().indexOf("ACCS_NBR")>-1)
						    result.append("<td><a href='prdview.do?method=list&nbrtype=accsnbr&nbr="+rs2.getString(i)+"'>" + rs2.getString(i) + "</a></td>");
						else if(dto.getBAS_TABLE_COL_NAME().toUpperCase().indexOf("PRD_INST_ID")>-1)
						    result.append("<td><a href='prdview.do?method=list&nbrtype=prdinstid&nbr="+rs2.getString(i)+"'>" + rs2.getString(i) + "</a></td>");

							result.append("<td>" + rs2.getString(i) + "</td>");
					}
				    result.append("</tr>");
				} else if (cimpage.getShowType().equals("v")) {//two columns one row
					int twoflag = 0;
					for (int i = 1; i <= iflag; i++) {
						twoflag = (twoflag + 1) % 2;
						inst = (CimPageAtom) pageatomlist.get(i - 1);
//						get biz_atom info from hashmap directly
						if(seglist.get(""+inst.getBizAtomId())!=null)
							dto=(SEG_BIZ_ATOM_DEFDTO)seglist.get(""+inst.getBizAtomId());
						else
						{
						   dto = (SEG_BIZ_ATOM_DEFDTO) atomdao.findById(inst.getBizAtomId());
						   seglist.put(""+inst.getBizAtomId(), dto);
						}
						//System.out.println(dto.getBAS_TABLE_COL_NAME());
						if(dto.getBAS_TABLE_COL_NAME().toUpperCase().indexOf("CUST_ID")>-1)
						   result.append("<td><img src='images/li2.gif' />"
								+ dto.getBIZ_ATOM_NAME() + ":<a href='custview.do?method=list&custid="+rs2.getString(i)+"'>"
								+ rs2.getString(i) + "</a></td>");
						else 
							result.append("<td><img src='images/li2.gif' />"
									+ dto.getBIZ_ATOM_NAME() + ":"
									+ rs2.getString(i) + "</td>");
						if (0 == twoflag)
							result.append("</tr><tr>");
					}
					if(1 == twoflag) result.append("<td></td>");
					result.append("</tr>");
				} else if (cimpage.getShowType().equals("x")) {//one columns one row,and include billing_cycle_id
					for (int i = 1; i <= iflag; i++) {
						inst = (CimPageAtom) pageatomlist.get(i - 1);
//						get biz_atom info from hashmap directly
						if(seglist.get(""+inst.getBizAtomId())!=null)
							dto=(SEG_BIZ_ATOM_DEFDTO)seglist.get(""+inst.getBizAtomId());
						else
						{
						   dto = (SEG_BIZ_ATOM_DEFDTO) atomdao.findById(inst.getBizAtomId());
						   seglist.put(""+inst.getBizAtomId(), dto);
						}
						result.append("<tr><td>"+rs2.getString(iflag+1)+"</td><td>"
								+ dto.getBIZ_ATOM_NAME() + "</td><td>"
								+ rs2.getString(i) + "</td></tr>");
     				}
				}
			}*/
		//System.out.println( result.toString());	
		//if(0==hasvalueflag)
				//result.append((new Constant()).getPara("NO_DATA"));
		/*if (cimpage.getShowType().equals("v"))
				result.append("</tr>");*/
		return result.toString();
	}
	
	public List findPageatom(String pageid) {
		List pageatomlist = (new CimPageAtomDAO()).findAll(pageid);
		return pageatomlist;
	}
	
	public String findPrdInstId(String latnid,String accsnbr) {
		return (new SysGetIdDAO()).findPrdInstId(latnid,accsnbr);
	}

	public String findcustidbycert(String latnid,String certnbr) {
		return (new SysGetIdDAO()).findcustidbycert(latnid,certnbr);
	}
}
